package data.deal;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.UUID;

import util.DateUtil;

import data.Constant;
import dataconnect.ConnectionFactory;
import dataconnect.SqlUtil;

public class SinaIndustryDaySort {

	public SinaIndustryDaySort(Date date) {
		String dataStr = DateUtil.format(date);

		SqlUtil.update("delete from Headstock where dealDate = '"+dataStr+"'");
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT s2.sinaIndustry2,AVG(ratepercent) FROM stockdaydeal s1 join stockinfo s2 on s1.number = s2.number \n");
		sql.append("WHERE s1.dealDay = '");
		sql.append(dataStr + "' AND s1.qty<>0 AND sinaIndustry2 is not null \n");
		sql.append("GROUP BY s2.sinaIndustry2 ORDER BY AVG(ratepercent) DESC ");

		Connection con = ConnectionFactory.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		Statement stmt2 = null;
		ResultSet rs2 = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql.toString());
			int industrySeq = 1;
			while (rs.next()) {
				String sql2 = "select s1.number,s1.ratepercent from Stockdaydeal s1,Stockinfo s2 where s1.dealDay='"
						+ dataStr
						+ "' and s1.number=s2.number and s2.sinaIndustry2='"
						+ rs.getString(1) + "' order by s1.ratepercent desc";
				stmt2 = con.createStatement();
				rs2 = stmt2.executeQuery(sql2);
				StringBuffer sql3 = new StringBuffer("insert Headstock values ");
				int stockSeq=1;
				while (rs2.next()) {
					if(rs2.getBigDecimal(2).compareTo(Constant.THREE)<0){
						sql3.setLength(sql3.length()-1);
						break;
					}
					String id = UUID.randomUUID().toString();
					sql3.append("('"+id+"',");
					sql3.append("'"+rs.getString(1)+"',");
					sql3.append("'"+dataStr+"',");
					sql3.append(rs.getBigDecimal(2));
					sql3.append(",'"+rs2.getString(1)+"',");
					sql3.append(rs2.getBigDecimal(2)+",");
					sql3.append(industrySeq+",");
					sql3.append(stockSeq+")");
					stockSeq++;
					if(stockSeq>3){
						break;
					}else{
						sql3.append(",");
					}
				}
				industrySeq++;
				if(stockSeq>1)
					SqlUtil.update(sql3.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (rs2 != null) {
					rs2.close();
				}
				if (stmt2 != null) {
					stmt2.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		String s = "2014-05-";
		for (int i = 1; i < 9; i++) {
			if (i % 7 == 3 || i % 7 == 4) {
				continue;
			}
			Date date = DateUtil.parseDate(s + i);
			String dataStr = DateUtil.format(date);
			System.out.println(dataStr);
			new SinaIndustryDaySort(date);
		}
	}
}
